create or replace 
PACKAGE BODY pkg_deploy_utils AS

   s_change_table    VARCHAR2(30) := 'T_DBDEPLOY_CHANGE_LOG';

   TYPE VC_LIST IS TABLE OF VARCHAR2( 30 );
   s_change_tabs    VC_LIST := VC_LIST( 'V_CHG_APPROVED_DBOBJ',    'V_CHG_UNAPPROVED_DBOBJ', 'V_CHG_APPROVED_DBDATA',    'V_CHG_UNAPPROVED_DBDATA', 'V_CHG_APPROVED_DBDM',    'V_CHG_UNAPPROVED_DBDM' );
   s_change_stgs    VC_LIST := VC_LIST( 'APPROVED_DB_OBJECTS',     'UNAPPROVED_DB_OBJECTS',  'APPROVED_DB_DATA',         'UNAPPROVED_DB_DATA',      'APPROVED_DB_DM',         'UNAPPROVED_DB_DM' );

   PROCEDURE install_dbdeploy_controls IS
      CURSOR c_chk( cp_table_name   IN    VARCHAR2 ) IS 
      SELECT '1'
      FROM user_tables
      WHERE UPPER( table_name ) = cp_table_name;

      l_res   VARCHAR2(30) := NULL;
      l_stmt  VARCHAR2(4000);
   BEGIN
      dbms_output.put_line( 'Starting to create the ' || s_change_table || ' items' );

      OPEN c_chk( s_change_table );
      FETCH c_chk INTO l_res;
      CLOSE c_chk;

      IF l_res IS NULL
      THEN
        l_stmt := 'CREATE TABLE ' || s_change_table || ' ( change_number NUMBER(22,0)     NOT NULL ' || CHR(10) ||
                                            ',complete_dt TIMESTAMP          NOT NULL ' || CHR(10) ||
                                            ',applied_by VARCHAR2(100)       NOT NULL ' || CHR(10) ||
                                            ',description VARCHAR2(500)      NOT NULL ' || CHR(10) ||
                                            ',change_stage  VARCHAR2(30)     NOT NULL  )';
                          
        dbms_output.put_line( 'Executing create statement: ' || l_stmt );
        EXECUTE IMMEDIATE l_stmt;

        dbms_output.put_line( 'Executing alter statement: ' || l_stmt );
        EXECUTE IMMEDIATE 'ALTER TABLE ' || s_change_table || ' ADD CONSTRAINT Pk' || s_change_table || ' PRIMARY KEY (change_stage,change_number)';
      ELSE
        dbms_output.put_line( 'The ' || s_change_table || ' items are already present - no action is required' );
      END IF;
      dbms_output.put_line( 'Finished creating the table: ' || s_change_table );

      FOR l_i IN 1..s_change_tabs.COUNT
      LOOP

        dbms_output.put_line( 'Creating view: ' || s_change_tabs( l_i ) );

        l_stmt := 'CREATE OR REPLACE VIEW ' || s_change_tabs( l_i ) || ' AS SELECT * FROM ' || s_change_table ||
                  ' WHERE change_stage = ''' ||  s_change_stgs( l_i ) || '''';

        dbms_output.put_line( 'Executing view statement: ' || l_stmt );
        EXECUTE IMMEDIATE l_stmt;

        dbms_output.put_line( 'Successfully created view: ' || s_change_tabs( l_i ) );

        l_stmt := 'CREATE OR REPLACE TRIGGER ' || 'TRG_' || s_change_tabs( l_i ) || CHR(10) ||
                  ' INSTEAD OF INSERT ON '  || s_change_tabs( l_i ) || CHR(10) ||
                  ' FOR EACH ROW ' || CHR(10) ||
                  ' BEGIN ' || CHR(10) ||
                  '   INSERT INTO ' || s_change_table || ' ( change_number, complete_dt, applied_by, description, change_stage ) ' || CHR(10) ||
                  '   VALUES ( :new.change_number, :new.complete_dt, :new.applied_by, :new.description, ''' || s_change_stgs( l_i ) || ''' );' || CHR(10) ||
                  'END;';

        dbms_output.put_line( 'Executing trigger statement: ' || l_stmt );

        EXECUTE IMMEDIATE l_stmt;

        dbms_output.put_line( 'Successfully created trigger on view: ' || s_change_tabs( l_i ) );
      END LOOP;

      dbms_output.put_line( 'Finished creating the ' || s_change_table || ' items' );
   END install_dbdeploy_controls;

   
END pkg_deploy_utils;
/
